package edu.gdin.ecommerce.util;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import jxl.format.Alignment;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import edu.gdin.ecommerce.model.Inventory;

public class ExportExcel {

	
	public static boolean inventoryByBatchNo(WritableWorkbook workBook, List<Inventory> inventories, String sheetTitle) {
		
		try {
			// 创建sheet
			WritableSheet sheet = workBook.createSheet(sheetTitle, 0);
			
			// 设置样式0
			WritableFont Bwf = new WritableFont(WritableFont.ARIAL, 18, WritableFont.NO_BOLD, false);
			WritableCellFormat format0 = new WritableCellFormat(Bwf);
			format0.setAlignment(Alignment.CENTRE);
			format0.setVerticalAlignment(VerticalAlignment.CENTRE);
			format0.setWrap(true);
			//format0.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
			// 设置样式1
			WritableFont Bwf1 = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false);
			WritableCellFormat format1 = new WritableCellFormat(Bwf1);
			format1.setAlignment(Alignment.CENTRE);
			format1.setVerticalAlignment(VerticalAlignment.CENTRE);
			format1.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
			format1.setBackground(Colour.ICE_BLUE);
			// 设置样式2
			WritableFont Bwf2 = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false);
			WritableCellFormat format2 = new WritableCellFormat(Bwf2);
			format2.setAlignment(Alignment.CENTRE);
			format2.setVerticalAlignment(VerticalAlignment.CENTRE);
			//format2.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
			
			sheet.setColumnView(0, 20);
			sheet.setColumnView(1, 10);
			sheet.setColumnView(2, 20);
			sheet.setColumnView(3, 15);
			sheet.setColumnView(4, 15);
			sheet.setColumnView(5, 45);
			sheet.setColumnView(6, 45);
			sheet.setColumnView(7, 15);
			
			// -- 开始设计表格 --
			int currRow = 0;
			sheet.mergeCells(0, currRow, 7, currRow);
			sheet.setRowView(currRow, 800);
			Label title = new Label(0, currRow, sheetTitle, format0);
			sheet.addCell(title);
			
			// 1
			currRow++;
			sheet.setRowView(currRow, 400);
			sheet.addCell(new Label(0, currRow, "卡号", format1));
			sheet.addCell(new Label(1, currRow, "发卡商", format1));
			sheet.addCell(new Label(2, currRow, "卡批次", format1));
			sheet.addCell(new Label(3, currRow, "卡类型", format1));
			sheet.addCell(new Label(4, currRow, "卡密码", format1));
			sheet.addCell(new Label(5, currRow, "第一磁道", format1));
			sheet.addCell(new Label(6, currRow, "第二磁道", format1));
			sheet.addCell(new Label(7, currRow, "卡状态", format1));
			
			// 1+n
			currRow++;
			for (Inventory i : inventories) {
				sheet.addCell(new Label(0, currRow, i.getInventoryNo(), format2));
				sheet.addCell(new Label(1, currRow, i.getCardIssuer().getName(), format2));
				sheet.addCell(new Label(2, currRow, i.getBatchNo(), format2));
				sheet.addCell(new Label(3, currRow, i.materialTypeToString(), format2));
				sheet.addCell(new Label(4, currRow, i.getPassword(), format2));
				sheet.addCell(new Label(5, currRow, i.getTracks1(), format2));
				sheet.addCell(new Label(6, currRow, i.getTracks2(), format2));
				sheet.addCell(new Label(7, currRow, i.statusToString(), format2));
				currRow++;
			}
			
			workBook.write();
			
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				workBook.close();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return true;
	}
	
	
	public static boolean importAccountFile(WritableWorkbook workBook, String sheetTitle) {
		
		try {
			// 创建sheet
			WritableSheet sheet = workBook.createSheet(sheetTitle, 0);
			
			// 设置样式0
			WritableFont Bwf = new WritableFont(WritableFont.ARIAL, 18, WritableFont.NO_BOLD, false);
			WritableCellFormat format0 = new WritableCellFormat(Bwf);
			format0.setAlignment(Alignment.CENTRE);
			format0.setVerticalAlignment(VerticalAlignment.CENTRE);
			format0.setWrap(true);
			//format0.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
			// 设置样式1
			WritableFont Bwf1 = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false);
			WritableCellFormat format1 = new WritableCellFormat(Bwf1);
			format1.setAlignment(Alignment.CENTRE);
			format1.setVerticalAlignment(VerticalAlignment.CENTRE);
			format1.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
			format1.setBackground(Colour.ICE_BLUE);
			// 设置样式2
			WritableFont Bwf2 = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false);
			WritableCellFormat format2 = new WritableCellFormat(Bwf2);
			format2.setAlignment(Alignment.CENTRE);
			format2.setVerticalAlignment(VerticalAlignment.CENTRE);
			//format2.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
			// 设置样式3
			WritableFont Bwf3 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
			WritableCellFormat format3 = new WritableCellFormat(Bwf3);
			format3.setAlignment(Alignment.LEFT);
			format3.setVerticalAlignment(VerticalAlignment.CENTRE);
			//format3.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
			
			sheet.setColumnView(0, 15);
			sheet.setColumnView(1, 15);
			sheet.setColumnView(2, 20);
			sheet.setColumnView(3, 20);
			sheet.setColumnView(4, 25);
			sheet.setColumnView(5, 30);
			sheet.setColumnView(6, 15);
			sheet.setColumnView(7, 15);
			sheet.setColumnView(8, 15);
			sheet.setColumnView(9, 15);
			sheet.setColumnView(10, 15);
			sheet.setColumnView(11, 20);
			sheet.setColumnView(12, 20);
			sheet.setColumnView(13, 15);
			sheet.setColumnView(14, 15);
			sheet.setColumnView(15, 15);
			sheet.setColumnView(16, 15);
			sheet.setColumnView(17, 20);
			sheet.setColumnView(18, 20);
			sheet.setColumnView(19, 20);
			sheet.setColumnView(20, 15);
			
			
			// -- 开始设计表格 --
			int currRow = 0;
			sheet.mergeCells(0, currRow, 20, currRow);
			sheet.setRowView(currRow, 800);
			Label title = new Label(0, currRow, "导入账户信息", format0);
			sheet.addCell(title);
			
			// 1
			currRow++;
			sheet.setRowView(currRow, 400);
			sheet.addCell(new Label(0, currRow, "客户姓名*", format1));
			sheet.addCell(new Label(1, currRow, "出生日期", format1));
			sheet.addCell(new Label(2, currRow, "手机号码*", format1));
			sheet.addCell(new Label(3, currRow, "固定电话", format1));
			sheet.addCell(new Label(4, currRow, "电子邮箱", format1));
			sheet.addCell(new Label(5, currRow, "现住地址", format1));
			sheet.addCell(new Label(6, currRow, "邮编", format1));
			sheet.addCell(new Label(7, currRow, "所在省*", format1));
			sheet.addCell(new Label(8, currRow, "所在市*", format1));
			sheet.addCell(new Label(9, currRow, "所在地区*", format1));
			sheet.addCell(new Label(10, currRow, "证件类型(编号)*", format1));
			sheet.addCell(new Label(11, currRow, "证件号码*", format1));
			sheet.addCell(new Label(12, currRow, "客户描述", format1));
			
			sheet.addCell(new Label(13, currRow, "账户名*", format1));
			sheet.addCell(new Label(14, currRow, "单次限额*", format1));
			sheet.addCell(new Label(15, currRow, "信用额度", format1));
			sheet.addCell(new Label(16, currRow, "积分", format1));
			sheet.addCell(new Label(17, currRow, "余额不足提醒(编号)*", format1));
			sheet.addCell(new Label(18, currRow, "账户描述", format1));
			
			sheet.addCell(new Label(19, currRow, "卡号码*", format1));
			sheet.addCell(new Label(20, currRow, "卡类型(编号)*", format1));
			
			// 2
			currRow++;
			List<String> importData = new ArrayList<String>();
			for (int i = 0; i < 10; i++) {
				importData.add("导入客户");
				importData.add("2012-05-01");
				importData.add("15017558420");
				importData.add("");
				importData.add("1017826124@qq.com");
				importData.add("广州市天河区中山大道293号");
				importData.add("510665");
				importData.add("广东省");
				importData.add("广州市");
				importData.add("天河区");
				importData.add("1");
				importData.add("12345678901234567");
				importData.add("");
				
				importData.add("导入账户");
				importData.add("1000");
				importData.add("5000");
				importData.add("0");
				importData.add("1");
				importData.add("");
				
				importData.add("0000000000000000");
				importData.add("1");
			}

			for (int i = 0; i < 21; i++) {
				sheet.addCell(new Label(i, currRow, importData.get(i), format2));
			}
			
			// 3
			currRow++;
			// 4
			currRow++;
			// 5
			currRow++;
			sheet.mergeCells(0, currRow, 4, currRow);
			sheet.addCell(new Label(0, currRow, "证件类型编号：(1=身份证,2=商业登记编号)", format3));
			// 6
			currRow++;
			sheet.mergeCells(0, currRow, 4, currRow);
			sheet.addCell(new Label(0, currRow, "余额不足提醒： (0=否,1=是)", format3));
			// 7
			currRow++;
			sheet.mergeCells(0, currRow, 4, currRow);
			sheet.addCell(new Label(0, currRow, "说明：带*号为必填项！ ！ 日期格式为 yyyy-MM-dd 或 yyyy/MM/dd ", format3));
			// 8
			currRow++;
			sheet.mergeCells(0, currRow, 4, currRow);
			sheet.addCell(new Label(0, currRow, "注意：导入数据中间不能留空行，数据从第3行开始，最后一行数据下面至少留一空行！", format3));
			
			workBook.write();
			
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				workBook.close();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return true;
	}
	
	
	
}
